﻿using System;
using System.Data;
using System.Text;
using System.Data.SQLite;

namespace Clipper.DAL
{
	/// <summary>
	/// 数据访问类:PatientInfo
	/// </summary>
    public partial class PatientInfo
    {
        public PatientInfo()
        { }
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(long patientID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from PatientInfo");
            strSql.Append(" where patientID=@patientID ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@patientID", DbType.Int64,8)			};
            parameters[0].Value = patientID;

            return SQLiteHelper.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.PatientInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into PatientInfo(");
			strSql.Append("patientID,name,sex,age,surgeryNumber,hospitalization,bedNumber,section,infectedPatch,createTime,note)");
			strSql.Append(" values (");
			strSql.Append("@patientID,@name,@sex,@age,@surgeryNumber,@hospitalization,@bedNumber,@section,@infectedPatch,@createTime,@note)");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@patientID", DbType.Int64,8),
					new SQLiteParameter("@name", DbType.String,20),
					new SQLiteParameter("@sex", DbType.String,2),
					new SQLiteParameter("@age", DbType.Int32,4),
					new SQLiteParameter("@surgeryNumber", DbType.Int32,4),
					new SQLiteParameter("@hospitalization", DbType.Int32,4),
					new SQLiteParameter("@bedNumber", DbType.Int32,4),
					new SQLiteParameter("@section", DbType.String,20),
					new SQLiteParameter("@infectedPatch", DbType.String,20),
					new SQLiteParameter("@createTime", DbType.DateTime),
					new SQLiteParameter("@note", DbType.String)};
			parameters[0].Value = model.patientID;
			parameters[1].Value = model.name;
			parameters[2].Value = model.sex;
			parameters[3].Value = model.age;
			parameters[4].Value = model.surgeryNumber;
			parameters[5].Value = model.hospitalization;
			parameters[6].Value = model.bedNumber;
			parameters[7].Value = model.section;
			parameters[8].Value = model.infectedPatch;
            parameters[9].Value = DateTime.Now;
			parameters[10].Value = model.note;

			int rows=SQLiteHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.PatientInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update PatientInfo set ");
			strSql.Append("name=@name,");
			strSql.Append("sex=@sex,");
			strSql.Append("age=@age,");
			strSql.Append("surgeryNumber=@surgeryNumber,");
			strSql.Append("hospitalization=@hospitalization,");
			strSql.Append("bedNumber=@bedNumber,");
			strSql.Append("section=@section,");
			strSql.Append("infectedPatch=@infectedPatch,");
			strSql.Append("createTime=@createTime,");
			strSql.Append("note=@note");
			strSql.Append(" where patientID=@patientID ");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@name", DbType.String,20),
					new SQLiteParameter("@sex", DbType.String,2),
					new SQLiteParameter("@age", DbType.Int32,4),
					new SQLiteParameter("@surgeryNumber", DbType.Int32,4),
					new SQLiteParameter("@hospitalization", DbType.Int32,4),
					new SQLiteParameter("@bedNumber", DbType.Int32,4),
					new SQLiteParameter("@section", DbType.String,20),
					new SQLiteParameter("@infectedPatch", DbType.String,20),
					new SQLiteParameter("@createTime", DbType.DateTime),
					new SQLiteParameter("@note", DbType.String),
					new SQLiteParameter("@patientID", DbType.Int64,8)};
			parameters[0].Value = model.name;
			parameters[1].Value = model.sex;
			parameters[2].Value = model.age;
			parameters[3].Value = model.surgeryNumber;
			parameters[4].Value = model.hospitalization;
			parameters[5].Value = model.bedNumber;
			parameters[6].Value = model.section;
			parameters[7].Value = model.infectedPatch;
			parameters[8].Value = model.createTime;
			parameters[9].Value = model.note;
			parameters[10].Value = model.patientID;

			int rows=SQLiteHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(long patientID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PatientInfo ");
            strSql.Append(" where patientID=@patientID ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@patientID", DbType.Int64,8)			};
            parameters[0].Value = patientID;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string patientIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PatientInfo ");
            strSql.Append(" where patientID in (" + patientIDlist + ")  ");
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.PatientInfo GetModel(long patientID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select patientID,name,sex,age,surgeryNumber,hospitalization,bedNumber,section,infectedPatch,createTime,note from PatientInfo ");
            strSql.Append(" where patientID=@patientID ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@patientID", DbType.Int64,8)			};
            parameters[0].Value = patientID;

            Model.PatientInfo model = new Model.PatientInfo();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.PatientInfo GetModel(long patientID,DateTime from,DateTime to)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select patientID,name,sex,age,surgeryNumber,hospitalization,bedNumber,section,infectedPatch,createTime,note from PatientInfo ");
            strSql.Append(" where  createTime>=@from and createTime<=@to and patientID=@patientID ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@from", DbType.DateTime),
                    new SQLiteParameter("@to",DbType.DateTime),
                    new SQLiteParameter("@patientID", DbType.Int64,8)
                                           };
            parameters[0].Value = from;
            parameters[1].Value = to;
            parameters[2].Value = patientID;
            Model.PatientInfo model = new Model.PatientInfo();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.PatientInfo DataRowToModel(DataRow row)
        {
            Model.PatientInfo model = new Model.PatientInfo();
            if (row != null)
            {
                if (row["patientID"] != null && row["patientID"].ToString() != "")
                {
                    model.patientID = long.Parse(row["patientID"].ToString());
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["sex"] != null)
                {
                    model.sex = row["sex"].ToString();
                }
                if (row["age"] != null && row["age"].ToString() != "")
                {
                    model.age = int.Parse(row["age"].ToString());
                }
                if (row["surgeryNumber"] != null && row["surgeryNumber"].ToString() != "")
                {
                    model.surgeryNumber = int.Parse(row["surgeryNumber"].ToString());
                }
                if (row["hospitalization"] != null && row["hospitalization"].ToString() != "")
                {
                    model.hospitalization = int.Parse(row["hospitalization"].ToString());
                }
                if (row["bedNumber"] != null && row["bedNumber"].ToString() != "")
                {
                    model.bedNumber = int.Parse(row["bedNumber"].ToString());
                }
                if (row["section"] != null)
                {
                    model.section = row["section"].ToString();
                }
                if (row["infectedPatch"] != null)
                {
                    model.infectedPatch = row["infectedPatch"].ToString();
                }
                if (row["createTime"] != null && row["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(row["createTime"].ToString());
                }
                if (row["note"] != null)
                {
                    model.note = row["note"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select patientID,name,sex,age,surgeryNumber,hospitalization,bedNumber,section,infectedPatch,createTime,note ");
            strSql.Append(" FROM PatientInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM PatientInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = SQLiteHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.patientID desc");
            }
            strSql.Append(")AS Row, T.*  from PatientInfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return SQLiteHelper.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@tblName", DbType.VarChar, 255),
                    new SQLiteParameter("@fldName", DbType.VarChar, 255),
                    new SQLiteParameter("@PageSize", DbType.Int32),
                    new SQLiteParameter("@PageIndex", DbType.Int32),
                    new SQLiteParameter("@IsReCount", DbType.bit),
                    new SQLiteParameter("@OrderType", DbType.bit),
                    new SQLiteParameter("@strWhere", DbType.VarChar,1000),
                    };
            parameters[0].Value = "PatientInfo";
            parameters[1].Value = "patientID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return SQLiteHelper.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

